#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Author:    御风
# @Mail:      zhong.yufeng@foxmail.com
# @Time:      2022/4/10 16:26


import re
import time
import datetime
import numpy as np
import pandas as pd
from sqlalchemy import Column, Integer, String, Date, DateTime
from sqlalchemy.exc import InternalError
from utils.db.module import Base, getModuleColumns
from utils.db.database import DataBase
from utils.helper import strFullToHalf, strToHump


class ProductItem(Base):
    """
    CREATE TABLE `sys_product` (
        `id` mediumint(8) NOT NULL AUTO_INCREMENT,
        `product_category_id` int(11) DEFAULT NULL,
        `product_category` varchar(255) DEFAULT NULL,
        `product_name` varchar(255) DEFAULT NULL,
        `price` varchar(255) DEFAULT '',
        `support_company` varchar(255) DEFAULT NULL,
        `deliver_area` varchar(255) DEFAULT '',
        `contact_peo` varchar(255) DEFAULT '',
        `contact_phone` varchar(255) DEFAULT NULL,
        `purchase_notes` varchar(255) DEFAULT NULL,
        `product_address` varchar(255) DEFAULT '',
        `address_type` varchar(255) DEFAULT '' COMMENT '1->pic adress, 2->article',
        `info_resourse` varchar(255) DEFAULT NULL,
        `release_time` date DEFAULT NULL,
        `create_time` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
        `create_peo` varchar(255) DEFAULT NULL,
        `update_time` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
        `update_peo` varchar(255) DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `product_category_index` (`product_category_id`) USING BTREE,
        CONSTRAINT `product_category_fk` FOREIGN KEY (`product_category_id`) REFERENCES `sys_category` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    """

    __tablename__ = "sys_product"

    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    product_category_id = Column(Integer, nullable=True)
    product_category = Column(String(255), nullable=True)
    product_name = Column(String(255), nullable=True)
    price = Column(String(255), nullable=True)
    support_company = Column(String(255), nullable=True)
    deliver_area = Column(String(255), nullable=True)
    contact_peo = Column(String(255), nullable=True)
    contact_phone = Column(String(255), nullable=True)
    purchase_notes = Column(String(255), nullable=True)
    product_address = Column(String(255), nullable=True)
    address_type = Column(String(255), nullable=True)
    info_resourse = Column(String(255), nullable=True)
    release_time = Column(Date, nullable=True)
    create_time = Column(DateTime, nullable=True)
    create_peo = Column(String(255), nullable=True)
    update_time = Column(DateTime, nullable=True)
    update_peo = Column(String(255), nullable=True)


class ProductDataBase(DataBase):

    def __init__(self):
        super(ProductDataBase, self).__init__()
        self.table = ProductItem.__tablename__

    def query(self, keywords: str, areaId: int, categoryId: int, pageNum: int = 1, pageSize: int = 20):
        """
        查询商品
        :param keywords: 搜索关键字，留空则为搜索全部
        :param areaId: 地区 ID，留空或 0 则为搜索全市
        :param categoryId: 分类 ID，留空或 0 则为搜索全部
        :param pageNum: 分页页数，留空或 0 则使用默认值 1，页数从 1 开始
        :param pageSize: 分页每页数据条数，留空或 0 则使用默认值 20
        :return:
        """
        if not isinstance(pageNum, int) or pageNum <= 0:
            pageNum = 1
        if not isinstance(pageSize, int) or pageSize <= 0:
            pageSize = 20

        # 先查地区
        areaName = None
        if isinstance(areaId, int) and areaId > 0:
            sql = f"SELECT `area_name` FROM `sys_deliver_area` WHERE `id`={areaId} LIMIT 0, 1;"
            df = pd.read_sql(sql, self.engine)
            cols = df["area_name"]
            areaName = None if len(cols) == 0 else cols[0]

            # 排除全部地区
            if areaName is not None:
                filterArea = ["上海市", "全市", "全部", "默认"]
                for area in filterArea:
                    if area in areaName:
                        areaName = None
                        break

        fieldsList = [
            "id",  # 商品 ID
            "product_category",  # 商品类型
            "product_name",  # 商品名称
            "price",  # 商品价格
            "deliver_area",  # 配送地区
            "support_company",  # 提供企业
            "contact_peo",  # 联系人
            "contact_phone",  # 联系电话
            "purchase_notes",  # 起送要求
            "product_address",  # 商品来源地址
            "address_type",  # 来源地址类型
            "info_resourse",  # 信息来源
            "release_time",  # 发布日期
        ]
        fields = { t: strToHump(t) for t in fieldsList }
        fieldsStr = ",".join([f"`{field}`" for field in fields.keys()])

        where = ""
        if isinstance(categoryId, int) and categoryId > 0:
            where = "WHERE" if where == "" else where
            where = f"{where} `product_category_id`= :categoryId"
        if areaName is not None:
            where = "WHERE" if where == "" else f"{where} AND"
            where = f"{where} (`deliver_area` LIKE '%%全市%%' OR `deliver_area` LIKE '%%' :areaName '%%')"
        if isinstance(keywords, str) and keywords != "":
            where = "WHERE" if where == "" else f"{where} AND"
            where = f"{where} (`product_category` LIKE '%%' :keywords '%%' OR `product_name` LIKE '%%' :keywords '%%')"

        dataList = []
        total = 0
        with self.engine.begin() as conn:
            offset = pageSize * (pageNum - 1)
            sql = f"SELECT SQL_CALC_FOUND_ROWS {fieldsStr} FROM `{self.table}` {where} ORDER BY `release_time` DESC, `product_category_id`, `update_time` DESC LIMIT {offset}, {pageSize};"
            cursor = conn.execute(self.text(sql), {
                "categoryId": categoryId,
                "areaName":   areaName,
                "keywords":   keywords,
            })
            rows = cursor.fetchall()
            cursor.close()
            if isinstance(rows, list):
                for row in rows:
                    data = { }
                    for i, v in enumerate(fields.values()):

                        # 输出字符处理
                        s = row[i]
                        if s is None:
                            s = ""
                        elif isinstance(s, (np.int32, np.int64)):
                            s = s.item()
                        elif isinstance(s, datetime.date):
                            s = str(s)
                        elif isinstance(s, str):
                            s = re.sub(r"[\r\n\s]+", " ", s).strip()
                        data[v] = s

                    # 地区除外处理
                    if areaName is not None:
                        deliverArea = strFullToHalf(data["deliverArea"])
                        if f"{areaName}除外" in deliverArea:
                            continue
                        matchs = re.findall(r"\((.*?)除外\)", deliverArea)
                        if matchs and areaName in matchs[0]:
                            continue

                    dataList.append(data)

            sql = "SELECT FOUND_ROWS() total;"
            cursor = conn.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            if isinstance(rows, list) and len(rows) == 1:
                total = rows[0][0]

        result = {
            "items":    dataList,
            "pageNum":  pageNum,
            "pageSize": pageSize,
            "total":    total,
        }
        return result

    def insert(self, dataList: list, user: str = None):
        currTime = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S")
        columns = getModuleColumns(ProductItem)
        fields = { strToHump(column): column for column in columns }
        result = False
        if isinstance(dataList, list):
            values = []
            for index, data in enumerate(dataList):
                if not isinstance(data, dict):
                    continue
                val = { }
                for k, v in fields.items():
                    t = data.get(k, None)
                    if t is None:
                        continue
                    elif (isinstance(columns[v], (int, Integer)) and not isinstance(t, int)) or (isinstance(columns[v], (str, String, Date, DateTime)) and not isinstance(t, str)):
                        raise ValueError(f"第 {index + 1} 条数据发生错误: 字段 {k} 的值 {t} 类型不能为 {type(t).__name__}")
                    if isinstance(columns[v], Date):
                        try:
                            time.strptime(t, "%Y-%m-%d")
                        except Exception:
                            raise ValueError(f"第 {index + 1} 条数据发生错误: 字段 {k} 的值 {t} 格式必须为日期格式")
                    elif isinstance(columns[v], DateTime):
                        try:
                            time.strptime(t, "%Y-%m-%d %H:%M:%S")
                        except Exception:
                            raise ValueError(f"第 {index + 1} 条数据发生错误: 字段 {k} 的值 {t} 格式必须为时间格式")
                    val[v] = t
                val["create_time"] = currTime
                val["create_peo"] = user
                val["update_time"] = currTime
                val["update_peo"] = user
                values.append(val)
            try:
                cursor = self.engine.execute(ProductItem.__table__.insert(), values)
                if cursor.rowcount > 0:
                    result = True
                cursor.close()
            except InternalError as e:
                # 数据重复
                if "Duplicate entry" in str(e):
                    # TODO: 数据重复是否回滚
                    return False
                raise e
        return result

    def delete(self, productIdList: list):
        result = None
        pids = ",".join(productIdList)
        sql = f"DELETE FROM `{self.table}` WHERE `id` in ({pids});"
        cursor = self.engine.execute(self.text(sql))
        if cursor.rowcount > 0:
            result = {
                "id":    productIdList,
                "count": cursor.rowcount,
            }
        cursor.close()
        return result

    def update(self, productId: int, data: dict, user: str = None):
        currTime = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S")
        columns = getModuleColumns(ProductItem)
        fields = { strToHump(column): column for column in columns }
        result = None

        setInfo = []
        for k, v in data.items():
            if k not in fields or k in ["id", "create_time", "create_peo", "update_time", "update_peo"]:
                continue
            fieldName = fields[k]
            tp = columns[fieldName]
            if tp is None:
                continue
            elif (isinstance(tp, (int, Integer)) and not isinstance(v, int)) or (isinstance(tp, (str, String, Date, DateTime)) and not isinstance(v, str)):
                raise ValueError(f"数据发生错误: 字段 {k} 的值 {v} 类型不能为 {type(v).__name__}")
            setInfo.append(f"`{fieldName}`=:{k}")
        setInfo.append(f"`update_time`=:time")
        setInfo.append(f"`update_peo`=:user")
        setData = ",".join(setInfo)

        dic = data.copy()
        dic["productId"] = productId
        dic["user"] = user
        dic["time"] = currTime
        sql = f"UPDATE `{self.table}` SET {setData} WHERE `id`=:productId LIMIT 1"
        cursor = self.engine.execute(self.text(sql), dic)
        if cursor.rowcount > 0:
            result = {
                "id": productId,
            }
        cursor.close()
        return result


if __name__ == "__main__":
    import json

    db = ProductDataBase()

    # print(json.dumps(db.query(areaId=0, categoryId=0, keywords="米", pageNum=1, pageSize=5), ensure_ascii=False, separators=(", ", ": ")))
    # print(json.dumps(db.query(areaId=11, categoryId=1, keywords="米", pageNum=1, pageSize=5), ensure_ascii=False, separators=(", ", ": ")))
    """
    {
        "product":  [
            {
                "id":             999, "productCategory": "粮油", "productName": "崇明晶润大米5kg", "price": "35元", "deliverArea": "浦东,奉贤", "supportCompany": "光明农发", "supportContact": "",
                "supportPhone":   "李经理13661420462", "purchaseNotes": "上海市市区每个团起订量100袋/单(以上三款可以组合购买)南汇、金山、临港奉贤地区每个团起订量200袋/单(以上三款可以组合购买",
                "productAddress": "https://mmbiz.qpic.cn/mmbiz_jpg/MJ1jCjwFy5OtRsvIaB3UZ9IeUwK6CtUrwicEwucFB0eK16mTHGiabH9V2zImEmulLMfCBB3KKH15hAj1iakYiavqvw/640?wx_fmt=jpeg&wxfrom=5&wx_lazy=1&wx_co=1",
                "addressType":    "", "infoResourse": "微信号：光明食品集团", "releaseTime": "2022-04-07"
            }, {
                "id":             1005, "productCategory": "粮油", "productName": "海丰优质大米10KG", "price": "58.8元", "deliverArea": "浦东,奉贤", "supportCompany": "光明农发",
                "supportContact": "", "supportPhone": "李经理13661420462", "purchaseNotes": "上海市市区每个团起订量100袋/单(以上三款可以组合购买)南汇、金山、临港奉贤地区每个团起订量200袋/单(以上三款可以组合购买",
                "productAddress": "https://mmbiz.qpic.cn/mmbiz_jpg/MJ1jCjwFy5OtRsvIaB3UZ9IeUwK6CtUrwicEwucFB0eK16mTHGiabH9V2zImEmulLMfCBB3KKH15hAj1iakYiavqvw/640?wx_fmt=jpeg&wxfrom=5&wx_lazy=1&wx_co=1",
                "addressType":    "", "infoResourse": "微信号：光明食品集团", "releaseTime": "2022-04-07"
            }, {
                "id":             1011, "productCategory": "粮油", "productName": "乐惠甄选虎林东北大米10KG", "price": "78元", "deliverArea": "浦东,奉贤", "supportCompany": "光明农发",
                "supportContact": "", "supportPhone": "李经理13661420462", "purchaseNotes": "上海市市区每个团起订量100袋/单(以上三款可以组合购买)南汇、金山、临港奉贤地区每个团起订量200袋/单(以上三款可以组合购买",
                "productAddress": "https://mmbiz.qpic.cn/mmbiz_jpg/MJ1jCjwFy5OtRsvIaB3UZ9IeUwK6CtUrwicEwucFB0eK16mTHGiabH9V2zImEmulLMfCBB3KKH15hAj1iakYiavqvw/640?wx_fmt=jpeg&wxfrom=5&wx_lazy=1&wx_co=1",
                "addressType":    "", "infoResourse": "微信号：光明食品集团", "releaseTime": "2022-04-07"
            }, {
                "id":             629, "productCategory": "粮油", "productName": "大米5kg", "price": "80元", "deliverArea": "浦东", "supportCompany": "越亚", "supportContact": "应经理",
                "supportPhone":   "16601809595/13917042323", "purchaseNotes": "30份起送",
                "productAddress": "https://mmbiz.qpic.cn/mmbiz_jpg/rtloRoxOGhG7quoCkfml1kbuyN6eoZaonXYL7wtTMiacHSlp97dwu9NOjHRRFbYTC1IO4JcAWCQfv7tmvKxj0TA/640?wx_fmt=jpeg&wxfrom=5&wx_lazy=1&wx_co=1",
                "addressType":    "", "infoResourse": "浦东三农", "releaseTime": "2022-04-07"
            }, {
                "id":             640, "productCategory": "蔬果/蛋品/粮油", "productName": "大米2.5千克", "price": "40元", "deliverArea": "浦东", "supportCompany": "山臻",
                "supportContact": "肽经理", "supportPhone": "18917000706 13061700168", "purchaseNotes": "30份起送",
                "productAddress": "https://mmbiz.qpic.cn/mmbiz_jpg/rtloRoxOGhG7quoCkfml1kbuyN6eoZaoF5eaBD7RoqRyBLhlhhS1ia5x8Ipekg2dMO5WRCKJUD6TY4aolPK4N6Q/640?wx_fmt=jpeg&wxfrom=5&wx_lazy=1&wx_co=1",
                "addressType":    "", "infoResourse": "浦东三农", "releaseTime": "2022-04-07"
            }],
        "pageNum":  1,
        "pageSize": 5,
        "total":    10
    }
    """

    # print(json.dumps(db.query(areaId=0, categoryId=0, keywords="", pageNum=1, pageSize=100), ensure_ascii=False, separators=(", ", ": ")))
    # print(json.dumps(db.query(areaId=0, categoryId=0, keywords="大米", pageNum=1, pageSize=100), ensure_ascii=False, separators=(", ", ": ")))

    # 注入测试
    # print(json.dumps(db.query(areaId=0, categoryId=0, keywords="' or 1=1) #", pageNum=1, pageSize=100), ensure_ascii=False, separators=(", ", ": ")))

    # 插入数据
    # arr = []
    # for i in range(0, 100):
    #     arr.append({
    #         "productCategoryId": i,
    #         "productCategory":   "product_category",
    #         "productName":       f"product_name_{i}",
    #         "price":             "price",
    #         "supportCompany":    "support_company",
    #         "deliverArea":       "deliver_area",
    #         "contactPeo":        "contact_peo",
    #         "contactPhone":      "contact_phone",
    #         "purchaseNotes":     "purchase_notes",
    #         "productAddress":    "product_address",
    #         "addressType":       "address_type",
    #         "infoResourse":      "info_resourse",
    #         "releaseTime":       "2022-04-09",
    #     })
    # print(db.insert(arr))

    # 更新
    # print(db.update(11, {
    #     "productCategoryId": 1,
    #     "productCategory":   "aaaaaaaaaa",
    #     "productName":       "bbbbbbbbbb",
    #     "price":             "price",
    #     "supportCompany":    "support_company",
    #     "deliverArea":       "deliver_area",
    #     "contactPeo":        "contact_peo",
    #     "contactPhone":      "contact_phone",
    #     "purchaseNotes":     "purchase_notes",
    #     "productAddress":    "product_address",
    #     "addressType":       "address_type",
    #     "infoResourse":      "info_resourse",
    #     "releaseTime":       "2022-04-09",
    # }))
